/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package eu.easyedu.dictionary.api.nservice.impl;

import eu.easyedu.dictionary.api.model.Dictionary;
import eu.easyedu.dictionary.api.model.Language;
import eu.easyedu.dictionary.api.model.Relationship;
import eu.easyedu.dictionary.api.model.Word;
import eu.easyedu.dictionary.api.model.WordRelationship;
import eu.easyedu.dictionary.api.nservice.spi.WordBookService;
import eu.easyedu.dictionary.api.util.Executable;
import eu.easyedu.persistence.Mapper;
import eu.easyedu.persistence.PersistenceProvider;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

/**
 *
 * @author hlavki
 */
public class WordBookServiceImpl implements WordBookService {

    private static final Logger log = Logger.getLogger(WordBookServiceImpl.class.getName());

    public List<Word> getRelatedWords(final Dictionary dict, final Word word, final Relationship relationship) {
        List<Word> result = PersistenceUtil.executeInPersitenceContext(dict, new Executable<List<Word>>() {

            public List<Word> execute(Connection conn, PersistenceProvider pu) throws SQLException {
                StringBuffer sql = new StringBuffer("SELECT ");
                Mapper<Word> wordMapper = pu.getEntityMapper(Word.class);
                Mapper<Language> langMapper = pu.getEntityMapper(Language.class);
                wordMapper.getSelectColumns(sql, "w").append(", ");
                langMapper.getSelectColumns(sql, "lang").append(" FROM WORD_RELATIONSHIP wr ");
                sql.append(" JOIN WORD w ON w.WORD_ID = wr.RELATED_WORD_ID ");
                sql.append(" JOIN LANGUAGE lang ON lang.LANGUAGE_ID = w.LANGUAGE_ID");
                sql.append(" WHERE wr.WORD_ID=? AND ");
                sql.append(" wr.RELATIONSHIP=?");
                log.info("SQL: " + sql.toString());
                PreparedStatement stmt = conn.prepareStatement(sql.toString());
                stmt.setLong(1, word.getId());
                stmt.setLong(2, relationship.ordinal());
                ResultSet rs = stmt.executeQuery();
                List<Word> result = new ArrayList<Word>();
                while (rs.next()) {
                    Word word = wordMapper.map(rs);
                    word.setLanguage(langMapper.map(rs));
                    result.add(word);
                }
                return result;
            }
        });
        return result;
    }

    public List<Word> getRelatedWords(final Dictionary dict, final String word, final Language lang, final Relationship relationship) {
        List<Word> result = PersistenceUtil.executeInPersitenceContext(dict, new Executable<List<Word>>() {

            public List<Word> execute(Connection conn, PersistenceProvider pu) throws SQLException {
                StringBuffer sql = new StringBuffer("SELECT ");
                Mapper<Word> wordMapper = pu.getEntityMapper(Word.class);
                Mapper<Language> langMapper = pu.getEntityMapper(Language.class);
                wordMapper.getSelectColumns(sql, "w").append(", ");
                langMapper.getSelectColumns(sql, "l");
                sql.append(" FROM WORD_RELATIONSHIP wr ").append("JOIN LANGUAGE l ON wr.LANGUAGE_ID=l.LANGUAGE_ID ");
                sql.append(" JOIN WORD w ON w.WORD_ID = wr.RELATED_WORD_ID WHERE wr.WORD_ID=? AND ");
                sql.append(" wr.RELATIONSHIP=?");
                log.info("SQL: " + sql.toString());
                PreparedStatement stmt = conn.prepareStatement(sql.toString());
//                stmt.setLong(1, word.getId());
                stmt.setLong(2, relationship.ordinal());
                ResultSet rs = stmt.executeQuery();
                List<Word> result = new ArrayList<Word>();
                while (rs.next()) {
                    Word word = wordMapper.map(rs);
                    word.setLanguage(langMapper.map(rs));
                    result.add(word);
                }
                return result;
            }
        });
        return result;
    }

    public boolean isRelatedTo(final Dictionary dict, Word word, Word subject, Relationship relationship) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean isRelatedTo(final Dictionary dict, String word, Language lang, Relationship relationship) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public Language getLanguage(final Dictionary dict, final String code) {
        return PersistenceUtil.getEntityById(dict, new Language(code), Language.class);
    }

    public Word getWord(final Dictionary dict, final String word, final Language lang) {
        Word result = PersistenceUtil.executeInPersitenceContext(dict, new Executable<Word>() {

            public Word execute(Connection conn, PersistenceProvider pu) throws SQLException {
                StringBuffer sql = new StringBuffer("SELECT ");
                Mapper<Word> wordMapper = pu.getEntityMapper(Word.class);
                Mapper<Language> langMapper = pu.getEntityMapper(Language.class);
                wordMapper.getSelectColumns(sql, "w").append(", ");
                langMapper.getSelectColumns(sql, "l");
                sql.append(" FROM WORD w ").append("JOIN LANGUAGE l ON w.LANGUAGE_ID=l.LANGUAGE_ID ");
                sql.append(" WHERE w.WORD=? AND w.LANGUAGE_ID=?");
                log.info("SQL: " + sql.toString());
                PreparedStatement stmt = conn.prepareStatement(sql.toString());
                stmt.setString(1, word);
                stmt.setLong(2, lang.getId());
                ResultSet rs = stmt.executeQuery();
                Word result = null;
                if (rs.next()) {
                    result = wordMapper.map(rs);
                    result.setLanguage(langMapper.map(rs));
                }
                return result;
            }
        });
        return result;
    }

    public Word getWord(final Dictionary dict, final String word, String lang) {
        final Long langId = getLanguage(dict, lang).getId();
        Word result = PersistenceUtil.executeInPersitenceContext(dict, new Executable<Word>() {

            public Word execute(Connection conn, PersistenceProvider pu) throws SQLException {
                StringBuffer sql = new StringBuffer("SELECT ");
                Mapper<Word> wordMapper = pu.getEntityMapper(Word.class);
                wordMapper.getSelectColumns(sql, "w").append(" FROM WORD w ");
                sql.append(" WHERE w.WORD LIKE '" + word + "' AND w.LANGUAGE_ID=?");
                log.info("SQL: " + sql.toString());
                PreparedStatement stmt = conn.prepareStatement(sql.toString());
                stmt.setLong(1, langId);
                ResultSet rs = stmt.executeQuery();
                rs.next();
                return wordMapper.map(rs);
            }
        });
        return result;
    }

    public List<Word> getAllWords(final Dictionary dict, final String lang) {
        List<Word> result = PersistenceUtil.executeInPersitenceContext(dict, new Executable<List<Word>>() {

            public List<Word> execute(Connection conn, PersistenceProvider pu) throws SQLException {
                StringBuffer sql = new StringBuffer("SELECT ");
                Mapper<Word> wordMapper = pu.getEntityMapper(Word.class);
                Mapper<Language> langMapper = pu.getEntityMapper(Language.class);
                wordMapper.getSelectColumns(sql, "w").append(", ");
                langMapper.getSelectColumns(sql, "lang").append(" FROM WORD w ");
                sql.append(" JOIN LANGUAGE lang ON w.LANGUAGE_ID = lang.LANGUAGE_ID WHERE lang.CODE=?");
                log.info("SQL: " + sql.toString());
                PreparedStatement stmt = conn.prepareStatement(sql.toString());
                stmt.setString(1, lang);
                ResultSet rs = stmt.executeQuery();
                List<Word> result = new ArrayList<Word>();
                while (rs.next()) {
                    Word word = wordMapper.map(rs);
                    word.setLanguage(langMapper.map(rs));
                    result.add(word);
                }
                return result;
            }
        });
        return result;
    }

    public List<Word> getAllWords(final Dictionary dict, final String word, final Language language, final SearchType type) {
        List<Word> result = PersistenceUtil.executeInPersitenceContext(dict, new Executable<List<Word>>() {

            public List<Word> execute(Connection conn, PersistenceProvider pu) throws SQLException {
                StringBuffer sql = new StringBuffer("SELECT ");
                Mapper<Word> wordMapper = pu.getEntityMapper(Word.class);
                Mapper<Language> langMapper = pu.getEntityMapper(Language.class);
                wordMapper.getSelectColumns(sql, "w").append(", ");
                langMapper.getSelectColumns(sql, "lang").append(" FROM WORD w ");
                sql.append(" JOIN LANGUAGE lang ON w.LANGUAGE_ID = lang.LANGUAGE_ID WHERE LOWER(w.WORD) LIKE ");
                switch (type) {
                    case ANYWHERE:
                        sql.append("'%").append(word.toLowerCase()).append("%'");
                        break;
                    case START_WITH:
                        sql.append("'").append(word.toLowerCase()).append("%'");
                        break;
                    case END_WITH:
                        sql.append("'%").append(word.toLowerCase()).append("'");
                        break;
                }
                sql.append(" AND lang.LANGUAGE_ID=").append(language.getId());
                log.info("SQL: " + sql.toString());
                CallableStatement stmt = conn.prepareCall(sql.toString());
//                stmt.setString(1, word);
                ResultSet rs = stmt.executeQuery();
                List<Word> result = new ArrayList<Word>();
                while (rs.next()) {
                    Word word = wordMapper.map(rs);
                    word.setLanguage(langMapper.map(rs));
                    result.add(word);
                }
                return result;
            }
        });
        return result;
    }

    public List<WordRelationship> getAllRelatedWords(final Dictionary dict, final Word word) {
        List<WordRelationship> result =
                PersistenceUtil.executeInPersitenceContext(dict, new Executable<List<WordRelationship>>() {

            public List<WordRelationship> execute(Connection conn, PersistenceProvider pu) throws SQLException {
                StringBuffer sql = new StringBuffer("SELECT ");
                Mapper<Word> wordMapper = pu.getEntityMapper(Word.class);
                Mapper<Language> langMapper = pu.getEntityMapper(Language.class);
                Mapper<WordRelationship> wrMapper = pu.getEntityMapper(WordRelationship.class);
                wrMapper.getSelectColumns(sql, "wr").append(", ");
                wordMapper.getSelectColumns(sql, "rw").append(", ");
                langMapper.getSelectColumns(sql, "lang").append(" FROM WORD_RELATIONSHIP wr ");
                sql.append("JOIN WORD rw ON wr.RELATED_WORD_ID = rw.WORD_ID ");
                sql.append("JOIN LANGUAGE lang ON lang.LANGUAGE_ID=rw.LANGUAGE_ID ");
                sql.append("WHERE wr.WORD_ID=?");
                log.info("SQL: " + sql.toString());
                PreparedStatement stmt = conn.prepareStatement(sql.toString());
                stmt.setLong(1, word.getId());
                ResultSet rs = stmt.executeQuery();
                List<WordRelationship> result = new ArrayList<WordRelationship>();
                while (rs.next()) {
                    WordRelationship relation = wrMapper.map(rs);
                    relation.setWord(word);
                    Word relatedWord = wordMapper.map(rs);
                    relatedWord.setLanguage(langMapper.map(rs));
                    relation.setRelatedWord(relatedWord);
                    result.add(relation);
                }
                return result;
            }
        });
        return result;
    }

    public List<Language> getAllLanguages(Dictionary dict) {
        List<Language> result = PersistenceUtil.executeInPersitenceContext(dict, new Executable<List<Language>>() {

            public List<Language> execute(Connection conn, PersistenceProvider pu) throws SQLException {
                StringBuffer sql = new StringBuffer("SELECT ");
                Mapper<Language> langMapper = pu.getEntityMapper(Language.class);
                langMapper.getSelectColumns(sql, "lang").append(" FROM LANGUAGE lang ");
                log.info("SQL: " + sql.toString());
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql.toString());
                List<Language> result = new ArrayList<Language>();
                while (rs.next()) {
                    Language lang = langMapper.map(rs);
                    result.add(lang);
                }
                return result;
            }
        });
        return result;
    }

    public Word addWord(final Dictionary dict, final Word word) {
        Word result = PersistenceUtil.executeInPersitenceContext(dict, new Executable<Word>() {

            public Word execute(Connection conn, PersistenceProvider pu) throws SQLException {
                String sql = "INSERT INTO WORD (WORD_ID, WORD_TYPE, WORD, LANGUAGE_ID, NOTES) VALUES (?, ?, ?, ?, ?)";
                PreparedStatement stmt = conn.prepareStatement(sql);
                Long id = getUniqueId(conn);
                stmt.setLong(1, id);
                stmt.setInt(2, word.getType().ordinal());
                stmt.setString(3, word.getWord());
                stmt.setLong(4, word.getLanguage().getId());
                stmt.setString(5, word.getNotes());
                stmt.executeUpdate();
                word.setId(id);
                return word;
            }
        });
        return result;
    }

    public Word addWord(final Dictionary dict, final Word word, final List<WordRelationship> relationships) {
        // Saving relationships
        Word result = addWord(dict, word);
        addRelationships(dict, relationships);
        return result;
    }

    public void addRelationships(final Dictionary dict, final List<WordRelationship> relationships) {
        PersistenceUtil.executeInPersitenceContext(dict, new Executable<Void>() {

            public Void execute(Connection conn, PersistenceProvider pu) throws SQLException {
                String sql = "INSERT INTO WORD_RELATIONSHIP (WORD_RELATIONSHIP_ID, WORD_ID, RELATED_WORD_ID, " +
                        "RELATIONSHIP) VALUES (?, ?, ?, ?)";
                PreparedStatement stmt = conn.prepareStatement(sql);
                for (WordRelationship relationship : relationships) {
                    Word relatedWord = relationship.getRelatedWord();
                    if (!relatedWord.exists()) {
                        addWord(dict, relatedWord);
                    }
                    stmt.setLong(1, getUniqueId(conn));
                    stmt.setLong(2, relationship.getWord().getId());
                    stmt.setLong(3, relationship.getRelatedWord().getId());
                    stmt.setInt(4, relationship.getRelationship().ordinal());
                    stmt.executeUpdate();
                    if (relationship.getRelationship().equals(Relationship.TRANSLATION)) {
                        stmt.setLong(1, getUniqueId(conn));
                        stmt.setLong(2, relationship.getRelatedWord().getId());
                        stmt.setLong(3, relationship.getWord().getId());
                        stmt.setInt(4, relationship.getRelationship().ordinal());
                        stmt.executeUpdate();
                    }
                }
                return null;
            }
        });
    }

    private Long getUniqueId(Connection conn) throws SQLException {
        Long result = null;
        String sql = "SELECT NEXT_HI FROM UNIQUE_KEY";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        rs.next();
        int uniqueId = rs.getInt(1) + 1;
        sql = "UPDATE UNIQUE_KEY SET NEXT_HI=NEXT_HI+1";
        stmt.executeUpdate(sql);
        result = new Long(uniqueId);
        return result;
    }

    public Language addLanguage(final Dictionary dict, final Language language) {
        Language result = PersistenceUtil.executeInPersitenceContext(dict, new Executable<Language>() {

            public Language execute(Connection conn, PersistenceProvider pu) throws SQLException {
                String sql = "INSERT INTO LANGUAGE (LANGUAGE_ID, NAME, CODE) VALUES (?, ?, ?)";
                log.info("Inserting SQL: " + sql);
                PreparedStatement stmt = conn.prepareStatement(sql);
                Long id = getUniqueId(conn);
                stmt.setLong(1, id);
                stmt.setString(2, language.getName());
                stmt.setString(3, language.getCode());
                stmt.executeUpdate();
                language.setId(id);
                return language;
            }
        });
        return result;
    }
}
